﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Dapper;
using Dapper.Contrib.Extensions;

namespace EC.Application.Impl
{
    using EC.Application.Interface;
    using EC.Entity;
    using EC.Entity.Enum;
    using EC.Entity.Query;
    using EC.Entity.Request.DepositOrder;
    using EC.Entity.Response.DepositOrder;
    using EC.Entity.Tables;
    using System.Data;

    /// <summary>
    /// 存款订单记录接口实现
    /// </summary>
    public class DepositOrderServiceImpl: Base,DepositOrderService
    {
        #region 注入
        /// <summary>
        /// 数据接口
        /// </summary>
        private readonly IDBSession _dbSession;

        /// <summary>
        /// 数据库上下文
        /// </summary>
        /// <param name="context">数据库上下文</param>
        public DepositOrderServiceImpl(IDBSession dbSession)
        {
            _dbSession = dbSession;
        }
        #endregion

        /// <summary>
        /// 获取
        /// </summary>
        /// <returns></returns>
        public DepositOrderResponse Get(int id)
        {
            var response = new JResult()
            {
                Success = false
            };

            var list = _dbSession.DbConnection.Query<DepositOrderResponse>("select * from depositorder where Id=@Id", new { Id = id });

            var model = list.FirstOrDefault();
            if (model == null)
            {
                return null;
            }

            return model;
        }

        /// <summary>
        /// 成功
        /// </summary>
        /// <returns></returns>
        public JResult Success(int id,int userId)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                _dbSession.Begin(IsolationLevel.Serializable);

                var depositOrders = _dbSession.DbConnection.Query<DepositOrder>("select * from depositorder where Id=@Id", new { Id = id }, _dbSession.DbTransaction);

                var depositOrder = depositOrders.FirstOrDefault();
                if (depositOrder == null)
                {
                    throw new Exception("暂无存款记录.");
                }

                if(depositOrder.Status == (int)DepositOrderStatus.成功)
                {
                    throw new Exception("状态已发生改变.");
                }
                //获取商户
                var merchantQuerySql = "SELECT * FROM Merchant where Id=@Id";

                var merchants = _dbSession.DbConnection.Query<Merchant>(merchantQuerySql, new { Id = depositOrder.MerchantId }, _dbSession.DbTransaction);
                var merchant = merchants.FirstOrDefault();
                if (merchant == null)
                {
                    throw new Exception("更新商户失败");
                }

                //余额日志
                var balanceLog = new BalanceLog()
                {
                    MerchantId = depositOrder.MerchantId,
                    OrderNo = depositOrder.Id,
                    Source = (int)BalanceLogSource.结算,
                    Type = (int)BalanceLogType.加,
                    Amount = depositOrder.JieSuanAmount,
                    BeforeBalance = merchant.DepositBalance,
                    AfterBalance = merchant.DepositBalance + depositOrder.JieSuanAmount,
                    Remark = $"结算订单(订单号:{depositOrder.Id} 订单金额:{depositOrder.JieSuanAmount})",
                    CreatedBy = userId,
                    CreatedDate = DateTime.Now
                };

                var balanceLogId = _dbSession.DbConnection.Insert<BalanceLog>(balanceLog, _dbSession.DbTransaction);

                //更新统计状态
                int updateDepositorderStatus = _dbSession.DbConnection.Execute("UPDATE depositorder SET DepositDate=@DepositDate,Status=@Status,Statistics=@Statistics WHERE Id=@Id", new { DepositDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), Status = (int)DepositOrderStatus.成功, Statistics = (int)DepositOrderStatistics.已统计, Id = id }, _dbSession.DbTransaction);
                if (updateDepositorderStatus <= 0)
                {
                    throw new Exception("更新统计状态失败");
                }

                //更新余额
                int merchantResult = _dbSession.DbConnection.Execute("UPDATE Merchant SET DepositBalance=DepositBalance+@Amount WHERE Id=@Id", new { Amount = depositOrder.JieSuanAmount, Id = depositOrder.MerchantId }, _dbSession.DbTransaction);
                if (merchantResult <= 0)
                {
                    throw new Exception("更新余额失败");
                }

                response.Success = true;
                response.Message = "操作成功.";

                //提交事务
                _dbSession.Commit();
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
                _dbSession.Rollback();
            }

            return response;
        }

        /// <summary>
        /// 重置
        /// </summary>
        /// <returns></returns>
        public JResult Reset(int id)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var depositOrders = _dbSession.DbConnection.Query<DepositOrder>("select * from depositorder where Id=@Id", new { Id = id });

                var depositOrder = depositOrders.FirstOrDefault();
                if (depositOrder == null)
                {
                    throw new Exception("暂无存款记录.");
                }

                if(depositOrder.NoticeStatus == (int)DepositOrderNoticeStatus.通知失败 && depositOrder.NoticeTimes == 5)
                {
                    var updateSql = "UPDATE depositorder SET NoticeStatus=@NoticeStatus,NoticeTimes=@NoticeTimes WHERE Id=@Id";

                    int result = _dbSession.DbConnection.Execute(updateSql, new { NoticeStatus = (int)DepositOrderNoticeStatus.未通知, NoticeTimes = 0, Id = depositOrder.Id });
                    if (result <= 0)
                    {
                        throw new Exception("更新失败");
                    }

                    response.Success = true;
                    response.Message = "操作成功.";
                }
                else
                {
                    throw new Exception("操作失败,必须为通知失败且通知次数等于5");
                }

            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 撤销
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult Revoke(RevokeRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };
            var depositOrders = _dbSession.DbConnection.Query<DepositOrder>("select * from depositorder where Id=@Id", new { Id = request.Id });

            var depositOrder = depositOrders.FirstOrDefault();
            if (depositOrder == null)
            {
                response.Message = "暂无存款记录、还未结算.";
                return response;
            }

            if (depositOrder.Statistics == (int)DepositOrderStatistics.未统计)
            {
                response.Message = "暂无存款记录、还未结算.";
                return response;
            }

            try
            {

                //事务开始
                _dbSession.Begin();


                var merchants = _dbSession.DbConnection.Query<Merchant>("select * from merchant where Id=@Id", new { Id = depositOrder.MerchantId }, _dbSession.DbTransaction);

                var merchant = merchants.FirstOrDefault();
                if (merchant == null)
                {
                    throw new Exception("暂无记录.");
                }

                //减去存款余额日志
                var depositBalanceLog = new BalanceLog()
                {
                    MerchantId = depositOrder.MerchantId,
                    Source = (int)BalanceLogSource.收款撤销,
                    Type = (int)BalanceLogType.减,
                    Amount = depositOrder.JieSuanAmount,
                    BeforeBalance = merchant.DepositBalance,
                    AfterBalance = merchant.DepositBalance - depositOrder.JieSuanAmount,
                    Remark = $"撤销订单 {depositOrder.Id}",
                    CreatedBy = request.UserId,
                    CreatedDate = DateTime.Now
                };

                //新增
                long depositBalanceLogResultId = _dbSession.DbConnection.Insert<BalanceLog>(depositBalanceLog, _dbSession.DbTransaction);

                if (depositBalanceLogResultId <= 0)
                {
                    throw new Exception("新增日志失败.");
                }

                //余额
                int resultMerchant = _dbSession.DbConnection.Execute("UPDATE merchant SET DepositBalance=DepositBalance-@Amount WHERE Id=@Id", new { Amount = depositOrder.JieSuanAmount, Id = depositOrder.MerchantId }, _dbSession.DbTransaction);
                if (resultMerchant <= 0)
                {
                    throw new Exception("更新账户失败");
                }

                var _updateSql = "UPDATE depositorder SET NoticeStatus=@NoticeStatus,Remark=@Remark WHERE Id=@Id";

                int _result = _dbSession.DbConnection.Execute(_updateSql, new { NoticeStatus = (int)DepositOrderNoticeStatus.不通知, Remark = "撤销", Id = depositOrder.Id }, _dbSession.DbTransaction);
                if (_result <= 0)
                {
                    throw new Exception("更新失败");
                }

                var updateSql = "UPDATE depositorder SET Status=1,NoticeStatus=5,Statistics=1 WHERE Id=@Id";

                int result = _dbSession.DbConnection.Execute(updateSql, new { Id = depositOrder.Id }, _dbSession.DbTransaction);
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
                response.Message = "撤销成功";
                //提交事务
                _dbSession.Commit();
            }
            catch (Exception ex)
            {
                _dbSession.Rollback();

                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<DepositOrderQueryResponse> Pager(DepositOrderQuery query)
        {
            var files = @"d.Id,
m.Name as MerchantName,
(SELECT Name FROM merchant WHERE Id=d.ProxyMerchantId) AS ProxyMerchantName,
MerchantOrderNo,
ClientCardName,
DepositAmount,
PayAmount,
JieSuanAmount,
LiRunAmount,
DepositDate,
DepositBankCardId,
DepositCardNumber,
(select BankCardName from bankcard where BankCardNumber=d.DepositCardNumber) as DepositCardName,
Transtype,
NoticeStatus,
NoticeLastTime,
NoticeTimes,
d.Status,
ReqeustBody,
NoticeFail,
Remark,
d.CreatedBy,
d.CreatedDate";
            var tableName = "depositorder d INNER JOIN merchant m on m.Id=d.MerchantId";
            var orderby = "ORDER BY Id DESC";
            var where = $"where d.MerchantId in ({string.Join(", ", query.MerchantIds)})";

            #region 条件
            if (query.Id.HasValue)
            {
                where += string.Format(" And d.Id ={0}", query.Id.Value);
            }

            if (query.Transtype.HasValue)
            {
                where += string.Format(" And d.Transtype ={0}", query.Transtype.Value);
            }

            if (!string.IsNullOrEmpty(query.MerchantOrderNo))
            {
                where += string.Format(" And d.MerchantOrderNo ='{0}'", query.MerchantOrderNo);
            }

            if (query.NoticeStatus.HasValue)
            {
                where += string.Format(" And d.NoticeStatus ='{0}'", query.NoticeStatus.Value);
            }

            if (query.Status.HasValue)
            {
                where += string.Format(" And d.Status ={0}", query.Status.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And d.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, (query.Page.Value-1) * query.Rows.Value, query.Rows.Value);

            var countSql = string.Format("SELECT sum(DepositAmount) AS DepositAmount,sum(PayAmount) as PayAmount,sum(JieSuanAmount) AS JieSuanAmount,sum(LiRunAmount) AS LiRunAmount FROM {1} {2};", files, tableName, where);

            var depositAmountTotal = new decimal(0);
            var payTotal = new decimal(0);
            var jieSuanTotal = new decimal(0);
            var liRunTotal = new decimal(0);

            var depositOrders = _dbSession.DbConnection.Query<DepositOrderTotal>(countSql);
            if (depositOrders != null && depositOrders.Any())
            {
                var depositOrder = depositOrders.FirstOrDefault();
                if(depositOrder!=null)
                {
                    depositAmountTotal = depositOrder.DepositAmount;
                    payTotal = depositOrder.PayAmount;
                    jieSuanTotal = depositOrder.JieSuanAmount;
                    liRunTotal = depositOrder.LiRunAmount;
                }
            }

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<DepositOrderQueryResponse>();

                var orderSum = new decimal(0);
                var paySum = new decimal(0);
                var jieSuanSum = new decimal(0);
                var liRunSum = new decimal(0);

                #region
                var rows = new List<DepositOrderQueryResponse>();

                foreach(var item in list)
                {
                    orderSum += item.DepositAmount;
                    paySum += item.PayAmount;
                    jieSuanSum += item.JieSuanAmount;
                    liRunSum += item.LiRunAmount;

                    var model = new DepositOrderQueryResponse();
                    model._Id = item.Id.ToString();
                    model.MerchantName = item.MerchantName;
                    model.MerchantOrderNo = item.MerchantOrderNo;
                    model.ClientCardName = item.ClientCardName;
                    model._DepositAmount = item.DepositAmount.ToString("#0.00");
                    model._PayAmount = item.PayAmount.ToString("#0.00");
                    model._JieSuanAmount = item.JieSuanAmount.ToString("#0.00");
                    model._LiRunAmount = item.LiRunAmount.ToString("#0.00");
                    model._DepositDate = item.DepositDate.HasValue ? item.DepositDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                    model.DepositCardNumber = item.DepositCardNumber;
                    model.DepositCardName = item.DepositCardName;
                    model.Transtype = item.Transtype;
                    model.NoticeStatus = item.NoticeStatus;
                    model._NoticeLastTime = item.NoticeLastTime.HasValue ? item.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                    model.NoticeTimes = item.NoticeTimes;
                    model.NoticeFail = item.NoticeFail;
                    model.Status = item.Status;
                    model.ProxyMerchantName = item.ProxyMerchantName;
                    model._CreatedDate = item.CreatedDate.ToString("yyyy-MM-dd HH:mm:ss");
                    model.CreatedDateMinutes = DateTime.Now.Subtract(item.CreatedDate).TotalMinutes;
                    if (query.UserPermission != null && query.UserPermission.Any(c=>c.Contains("DepositOrder0002")))
                    {
                        model.SupplementOrderPermission = "DepositOrder0002";
                    }
                    if (query.UserPermission != null && query.UserPermission.Any(c => c.Contains("DepositOrder0003")))
                    {
                        model.RevokePermission = "DepositOrder0003";
                    }
                    if (query.UserPermission != null && query.UserPermission.Any(c => c.Contains("DepositOrder0004")))
                    {
                        model.ResetPermission = "DepositOrder0004";
                    }
                    rows.Add(model);
                }
                #endregion

                //定义页脚
                var footers = new List<DepositOrderQueryResponse>() {

                    new DepositOrderQueryResponse()
                    {
                        ProxyMerchantName = "小计",
                        _DepositAmount = orderSum.ToString("#0.00"),
                        _PayAmount = paySum.ToString("#0.00"),
                        _JieSuanAmount = jieSuanSum.ToString("#0.00"),
                        _LiRunAmount = liRunSum.ToString("#0.00"),
                    },

                    new DepositOrderQueryResponse()
                    {

                        ProxyMerchantName = "总计",
                        _DepositAmount = depositAmountTotal.ToString("#0.00"),
                        _PayAmount = payTotal.ToString("#0.00"),
                        _JieSuanAmount = jieSuanTotal.ToString("#0.00"),
                        _LiRunAmount = liRunTotal.ToString("#0.00"),
                    }

                };

                return new DataGrid<DepositOrderQueryResponse>()
                {
                    Total = count,
                    Rows = rows,
                    Footer = footers,
                    Success = true
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<DepositOrderQueryResponse> ExportPager(DepositOrderQuery query)
        {
            //var currentPageIndex = 0;

            //if (query.CurrentPageIndex.Value > 0)
            //{
            //    currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            //}

            var files = "d.Id,m.Name as MerchantName,(SELECT Name FROM merchant WHERE Id=d.ProxyMerchantId) AS ProxyMerchantName,MerchantOrderNo,ClientCardName,DepositAmount,PayAmount,JieSuanAmount,LiRunAmount,DepositDate,DepositBankCardId,DepositCardNumber,Transtype,NoticeStatus,NoticeLastTime,NoticeTimes,d.Status,ReqeustBody,NoticeFail,Remark,d.CreatedBy,d.CreatedDate";
            var tableName = "depositorder d INNER JOIN merchant m on m.Id=d.MerchantId";
            var orderby = "ORDER BY Id DESC";
            var where = $"where 1=1";

            #region 条件
            if(query.MerchantIds!=null && query.MerchantIds.Any())
            {
                where += $" And d.MerchantId in ({string.Join(", ", query.MerchantIds)})";
            }

            if (!string.IsNullOrEmpty(query.MerchantOrderNo))
            {
                where += string.Format(" And d.MerchantOrderNo ='{0}'", query.MerchantOrderNo);
            }

            if (query.NoticeStatus.HasValue)
            {
                where += string.Format(" And d.NoticeStatus ='{0}'", query.NoticeStatus.Value);
            }

            if (query.Status.HasValue)
            {
                where += string.Format(" And d.Status ={0}", query.Status.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And d.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3};", files, tableName, where, orderby);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<DepositOrderQueryResponse>();

                var orderSum = new decimal(0);
                var paySum = new decimal(0);
                var jieSuanSum = new decimal(0);
                var liRunSum = new decimal(0);

                #region
                var rows = new List<DepositOrderQueryResponse>();

                foreach (var item in list)
                {
                    orderSum += item.DepositAmount;
                    paySum += item.PayAmount;
                    jieSuanSum += item.JieSuanAmount;
                    liRunSum += item.LiRunAmount;

                    var model = new DepositOrderQueryResponse();
                    model._Id = item.Id.ToString();
                    model.MerchantName = item.MerchantName;
                    model.MerchantOrderNo = item.MerchantOrderNo;
                    model.ClientCardName = item.ClientCardName;
                    model._DepositAmount = item.DepositAmount.ToString("#0.00");
                    model._PayAmount = item.PayAmount.ToString("#0.00");
                    model._JieSuanAmount = item.JieSuanAmount.ToString("#0.00");
                    model._LiRunAmount = item.LiRunAmount.ToString("#0.00");
                    model._DepositDate = item.DepositDate.HasValue ? item.DepositDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                    model.DepositCardNumber = item.DepositCardNumber;
                    model.Transtype = item.Transtype;
                    model.NoticeStatus = item.NoticeStatus;
                    model._NoticeLastTime = item.NoticeLastTime.HasValue ? item.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                    model.NoticeTimes = item.NoticeTimes;
                    model.NoticeFail = item.NoticeFail;
                    model.Status = item.Status;
                    model.ProxyMerchantName = item.ProxyMerchantName;
                    model._CreatedDate = item.CreatedDate.ToString("yyyy-MM-dd HH:mm:ss");
                    model.CreatedDateMinutes = DateTime.Now.Subtract(item.CreatedDate).TotalMinutes;
                    rows.Add(model);
                }
                #endregion

                //定义页脚
                var footers = new List<DepositOrderQueryResponse>() {
                    new DepositOrderQueryResponse()
                    {
                        _Id = null,
                        MerchantName = "",
                        ProxyMerchantName = "总额",
                        _DepositAmount = orderSum.ToString("#0.00"),
                        _PayAmount = paySum.ToString("#0.00"),
                        _JieSuanAmount = jieSuanSum.ToString("#0.00"),
                        _LiRunAmount = liRunSum.ToString("#0.00"),
                    }
                };

                return new DataGrid<DepositOrderQueryResponse>()
                {
                    Total = count,
                    Rows = rows,
                    Footer = footers,
                    Success = true
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<DepositOrderQueryResponse> SyPager(DepositOrderQuery query)
        {
            //var currentPageIndex = 0;

            //if (query.CurrentPageIndex.Value > 0)
            //{
            //    currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            //}

            var files = "d.Id,m.Name as MerchantName,(SELECT Name FROM merchant WHERE Id=d.ProxyMerchantId) AS ProxyMerchantName,MerchantOrderNo,ClientCardName,DepositAmount,PayAmount,JieSuanAmount,LiRunAmount,DepositDate,DepositBankCardId,DepositCardNumber,Transtype,NoticeStatus,NoticeLastTime,NoticeTimes,d.Status,ReqeustBody,NoticeFail,Remark,d.CreatedBy,d.CreatedDate";
            var tableName = "depositorder d INNER JOIN merchant m on m.Id=d.MerchantId";
            var orderby = "ORDER BY d.CreatedDate DESC";
            var where = $"where 1=1";
            //var where = $"where d.MerchantId in ({string.Join(", ", query.MerchantIds)})";

            #region 条件
            if (!string.IsNullOrEmpty(query.MerchantOrderNo))
            {
                where += string.Format(" And d.MerchantOrderNo ='{0}'", query.MerchantOrderNo);
            }

            if (query.NoticeStatus.HasValue)
            {
                where += string.Format(" And d.NoticeStatus ='{0}'", query.NoticeStatus.Value);
            }

            if (query.Status.HasValue)
            {
                where += string.Format(" And d.Status ={0}", query.Status.Value);
            }

            if(query.MerchantId.HasValue)//所属商户ID
            {
                where += string.Format(" And d.MerchantId ={0}", query.MerchantId.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And d.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, query.Page.Value - 1, query.Rows.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<DepositOrderQueryResponse>();

                var paySum = new decimal(0);
                var jieSuanSum = new decimal(0);
                var liRunSum = new decimal(0);

                #region
                var rows = new List<DepositOrderQueryResponse>();

                foreach (var item in list)
                {
                    paySum += item.PayAmount;
                    jieSuanSum += item.JieSuanAmount;
                    liRunSum += item.LiRunAmount;

                    var model = new DepositOrderQueryResponse();
                    model._Id = item.Id.ToString();
                    model.MerchantName = item.MerchantName;
                    model.MerchantOrderNo = item.MerchantOrderNo;
                    model.ClientCardName = item.ClientCardName;
                    model._DepositAmount = item.DepositAmount.ToString("#0.00");
                    model._PayAmount = item.PayAmount.ToString("#0.00");
                    model._JieSuanAmount = item.JieSuanAmount.ToString("#0.00");
                    model._LiRunAmount = item.LiRunAmount.ToString("#0.00");
                    model._DepositDate = item.DepositDate.HasValue ? item.DepositDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                    model.DepositCardNumber = item.DepositCardNumber;
                    model.Transtype = item.Transtype;
                    model.NoticeStatus = item.NoticeStatus;
                    model._NoticeLastTime = item.NoticeLastTime.HasValue ? item.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                    model.NoticeTimes = item.NoticeTimes;
                    model.NoticeFail = item.NoticeFail;
                    model.Status = item.Status;
                    model.ProxyMerchantName = item.ProxyMerchantName;
                    model._CreatedDate = item.CreatedDate.ToString("yyyy-MM-dd HH:mm:ss");
                    model.CreatedDateMinutes = DateTime.Now.Subtract(item.CreatedDate).TotalMinutes;
                    rows.Add(model);
                }
                #endregion

                //定义页脚
                var footers = new List<DepositOrderQueryResponse>() {
                    new DepositOrderQueryResponse()
                    {
                        _Id = null,
                        MerchantName = "",
                        ProxyMerchantName = "总额",
                        _PayAmount = paySum.ToString("#0.00"),
                        _JieSuanAmount = jieSuanSum.ToString("#0.00"),
                        _LiRunAmount = liRunSum.ToString("#0.00"),
                    }
                };

                return new DataGrid<DepositOrderQueryResponse>()
                {
                    Total = count,
                    Rows = rows,
                    Footer = footers,
                    Success = true
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<DepositOrderStatisticsQueryResponse> StatisticsPager(DepositOrderQuery query)
        {
            var files = "SUM(DepositAmount) AS DepositAmount,SUM(JieSuanAmount) AS JieSuanAmount,SUM(LiRunAmount) AS LiRunAmount";
            var tableName = "depositorder";
            var orderby = "ORDER BY CreatedDate DESC";
            var where = $"where 1=1";

            #region 条件
            if (!string.IsNullOrEmpty(query.MerchantOrderNo))
            {
                where += string.Format(" And MerchantOrderNo ='{0}'", query.MerchantOrderNo);
            }

            if (query.NoticeStatus.HasValue)
            {
                where += string.Format(" And NoticeStatus ='{0}'", query.NoticeStatus.Value);
            }

            if (query.Status.HasValue)
            {
                where += string.Format(" And Status ={0}", query.Status.Value);
            }

            if (query.MerchantId.HasValue)
            {
                where += string.Format(" And MerchantId ={0}", query.MerchantId.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, query.Page.Value - 1, query.Rows.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<DepositOrderStatisticsQueryResponse>();

                return new DataGrid<DepositOrderStatisticsQueryResponse>()
                {
                    Total = count,
                    Rows = list.ToList(),
                    //Footer = footers,
                    Success = true
                };
            }
        }
    }
}
